<?php
	require 'CostLib.php';
	/*Inserts a new sample in the database*/
	function InsertNewSampleEntry($casl_no_year, $casl_no_sample, $sampling_date,
		 $sample_type, $sample_category, $sample_code,$main_type, $sub_type, 
		 $analysis_required, $cost,  $analyst,  $remarks, 
		$sample_key, $database, $host, $login, $loginpassword,$tablename){ 
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$addSampleQuery = "INSERT INTO $tablename (sample_key, casl_no_year, casl_no_sample, 
			status, sampling_date, sample_type, sample_category, sample_code, main_type, sub_type,
			analysis_required, cost, analyst, remarks) VALUES ('$sample_key' ,'$casl_no_year',
			'$casl_no_sample', 'Ongoing', '$sampling_date', '$sample_type', '$sample_category',
			'$sample_code','$main_type', '$sub_type','$analysis_required', '$cost', '$analyst',
			'$remarks')";
		$addSample = mysql_query($addSampleQuery);
		echo ($addSample == NULL);
		echo $addSampleQuery."</br>";
		//Close connection.
		mysql_close($connect);
		if ($addSample == TRUE) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	function UpdateSampleEntry($casl_no_year, $casl_no_sample, $sampling_date,
		 $sample_type, $sample_category, $sample_code,$main_type, $sub_type, 
		 $analysis_required, $cost,  $analyst,  $remarks, 
		$sample_key, $database, $host, $login, $loginpassword,$tablename){ 
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$addSampleQuery = "UPDATE $tablename 
			SET sampling_date ='$sampling_date' ,
			sample_type = '$sample_type' ,
			sample_category ='$sample_category' ,
			sample_code ='$sample_code' ,
			main_type ='$main_type' ,
			sub_type ='$sub_type' ,
			analysis_required ='$analysis_required' ,
			cost = '$cost' ,
			analyst = '$analyst' ,
			remarks = '$remarks'
			WHERE
			casl_no_year ='$casl_no_year'
			AND casl_no_sample = '$casl_no_sample' ";
	//	echo $addSampleQuery."</br>";
		$addSample = mysql_query($addSampleQuery);
	//	echo "|".$addSample."|</br>";
		//Close connection.
		mysql_close($connect);
		if ($addSample == TRUE) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	function countSamples($sample_key,$database,$host,$login,$loginpassword,$tablename){
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$query = "SELECT COUNT(casl_no_sample) FROM $tablename WHERE sample_key = '$sample_key'";
		
		$samplecount = mysql_query($query);
		$sample = mysql_fetch_array($samplecount);
		
		//Close connection.
		mysql_close($connect);
		if ($samplecount != NULL) {
			return $sample[0];
		} else {
			return FALSE;
		}
	}
	
	function getSamplingDate($sample_key,$database,$host,$login,$loginpassword,$tablename){
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$query = "SELECT date_created FROM $tablename WHERE sample_key = '$sample_key'";
		
		$samplecount = mysql_query($query);
		$sample = mysql_fetch_array($samplecount);
		
		//Close connection.
		mysql_close($connect);
		if ($samplecount != NULL) {
			return $sample[0];
		} else {
			return FALSE;
		}
	}
	
	
	function DisplayAllAnalysisTypes($database,$host,$login,$loginpassword){
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$result = mysql_query("SELECT DISTINCT sample_type FROM cost_analysis");
		$listall= NULL;
		$inti=0;
		while($row = mysql_fetch_array($result) ){
			echo '<option value="'.$row["sample_type"].'">'.$row["sample_type"].'</option>';
		}
				
		mysql_close($connect);
	}

	function DisplayAllAnalysisSubtypes($database,$host,$login,$loginpassword,$analysis_type){
		//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		
		//Set-up and execute query.
		$result = mysql_query("SELECT analysis_type FROM cost_analysis WHERE sample_type='$analysis_type'");
		$listall= NULL;
		$inti=0;
		echo "<div style='font:13.3px sans-serif;width:12em;border-left:1px solid #808080;border-top:1px solid #808080;border-bottom:1px solid #fff; border-right:1px solid #fff;'><div style='background:#fff; overflow:auto;height:7.1em;border-left:1px solid #404040;border-top:1px solid #404040;border-bottom:1px solid #d4d0c8;border-right:1px solid #d4d0c8;'>";
		while($row = mysql_fetch_array($result)){
			echo "<label for='cb$inti' style='padding-right:3px;display:block;'><input name='checkbox[]' value='$inti' type='checkbox' id='cb$inti' onclick='highlight_div(this);'>$row[ANALYSIS_TYPE]</label>";
			$inti++;
		}
		echo "</div></div>";
		mysql_close($connect);
	}
	
	function drawEditSampleDetailTable($sample_key,$database,$host,$login,$loginpassword,$tablename){
	//Create connection.
		$connect =
				mysql_connect($host, $login, $loginpassword);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		mysql_select_db($database, $connect);
		//Set-up and execute query.
		$query = "SELECT * FROM $tablename WHERE sample_key = '$sample_key'";
		$samplecount = mysql_query($query);
		
		
				$y = 1;
		while($sample = mysql_fetch_array($samplecount)){
				echo "<tr>";
				printf("<td>%04d-%04d</td>",$sample["casl_no_year"],$sample["casl_no_sample"]);
				echo "<input type='hidden' name='casl_no_year[]' value = '".$sample["casl_no_year"]."'/>";
				echo "<input type='hidden' name='casl_no_sample[]' value = '".$sample["casl_no_sample"]."'/>";
			
				
				echo "<td><select name = 'sample_category[]' id='sample_category' >
										<option value='water'>Water</option>
										<option value='food'>Food</option>
										<option value='feed'>Feed</option>
										<option value='soil'>Soil</option>
										<option value='plant'>Plant</option>
										<option value='other'>Others...</option>
				</select></td>";
				echo "<td>";
				echo "<select id = 'analysis_required$y' name = 'analysis_required[]'>";
				$mainType = getMainTypes($database,$host,"login","login");
				for($i = 0; $i < count($mainType); $i++){
					echo "<optgroup label='$mainType[$i]'>";
					$sampleType = getSampleTypes($mainType[$i],$database,$host,"login","login");
					for($j = 0; $j < count($sampleType); $j++){
						echo "<optgroup label='$sampleType[$j]'>";
						$analysisType = getAnalysisTypes($mainType[$i],$sampleType[$j],$database,$host,"login","login");
						for($k = 0; $k < count($analysisType); $k++){
							echo "<option value='$analysisType[$k]/$mainType[$i]/$sampleType[$j]'";
							echo ">";
							echo $analysisType[$k];
							echo "</option>";
						}							
						echo "</optgroup>";
					}
					echo "</optgroup>";
				}
				echo "</select>";
				echo "</td>";
				
				echo "<td><input type='text' name='sample_code[]' maxlength='30'/></td>";
				echo "<td><textarea name='remarks[]' rows='2' cols='40' maxlength='1000'></textarea></td>";
			//	echo "<td><input type='text' name='remarks' maxlength='30'/></td>";
				echo "</tr>";
				$y++;
		}
		
		//Close connection.
		mysql_close($connect);
		if ($samplecount != NULL) {
			return $sample[0];
		} else {
			return FALSE;
		}
		
		
		mysql_close($connect);
	
	}
	
	function getAnalysts($database,$host,$login,$loginPassword){
		$returnValue = array();
		$con = mysql_connect($host,$login,$loginPassword) or die('Could not connect: ' . mysql_error());
		mysql_select_db($database) or die(mysql_error());
		$query = "SELECT name FROM users u, login l WHERE u.username = l.username AND l.usertype = 'ANALYST'";
		//echo $query;
		$result = mysql_query("SELECT name FROM users u, login l WHERE u.username = l.username AND l.usertype = 'ANALYST'")or die(mysql_error());
		while ($row = mysql_fetch_array($result)){
			array_push($returnValue, $row[0]);
		//	echo $row[0];
		}	
		mysql_close($con);
		return $returnValue;
	}
	
	function drawAnalystTable($database,$host,$login,$loginPassword){
		$analysts= getAnalysts($database,$host,$login,$loginPassword);
		echo "<select id = 'analyst' name = 'analyst'>";
		for($i = 0; $i < count($analysts); $i++){
			echo "<option value='$analysts[$i]'> $analysts[$i]";
							echo "</option>";
		}
		echo "</select>";
		
	}
	
	function insertSampleParameters($casl_year, $casl_sample, $mainType, $sampleType, $analysisType, $database, $host, $login, $password){
		$con = mysql_connect($host,$login,$password) or die('Could not connect: ' . mysql_error());
		mysql_select_db($database) or die(mysql_error());
		$query = "SELECT id FROM analysis WHERE main_type = '". $mainType."'  and sample_type = '". $sampleType
			. "' and ANALYSIS_TYPE = '". $analysisType."'" ;
		$result = mysql_query($query);
		while ($row = mysql_fetch_array($result)){
			$query = "INSERT INTO edit_analysis (casl_year, casl_sample, parameter_id, value1, value2, value3) VALUES ($casl_year,$casl_sample,$row[0],'','','')";
			mysql_query($query);
		}
		$addResult = "INSERT INTO analysis_result (casl_year, casl_sample, unit, result_value1, result_value2) VALUES ($casl_year,$casl_sample, '', '', '')";
		mysql_query($addResult);
		mysql_close($con);
	}
?>